﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Windows.Forms;
using System.IO;

namespace ufolder
{
    public class Item
    {
        private int _id;
        private string _titre;
        private string _uri;
        private string _texte;
        private string _date;
        private string _type;
        private Label[] _tags;

        private string _error="";

        public string Error
        {
            get
            {
                return this._error;
            }
        }

        public Item(int id, string titre, string uri, string texte, string date, string type, Label[] tags)
        {
            this._id = id;
            this._titre = titre;
            this._uri = uri;
            this._texte = texte;
            this._date = date;
            this._type = type;
            this._tags = tags;
        }

        public Label[] Tags
        {
            get{
                return this._tags;
            }
            
        }

        public string Titre
        {
            get
            {
                return this._titre;
            }
        }

        public string Texte
        {
            get
            {
                return this._texte;
            }
        }

        public string URI
        {
            get
            {
                return this._uri;
            }
        }

        public string Date
        {
            get
            {
                return this._date;
            }
        }

        public int Id
        {
            get
            {
                return this._id;
            }
        }

        public string Type
        {
            get
            {
                return this._type;
            }
        }

        public static string GetTypeFromURI(string uri)
        {
            string t = null;
            if (uri.ToLower().StartsWith(@"a:\")
                || uri.ToLower().StartsWith(@"b:\")
                || uri.ToLower().StartsWith(@"c:\")
                || uri.ToLower().StartsWith(@"d:\")
                || uri.ToLower().StartsWith(@"e:\")
                || uri.ToLower().StartsWith(@"f:\")
                || uri.ToLower().StartsWith(@"g:\")
                || uri.ToLower().StartsWith(@"h:\")
                || uri.ToLower().StartsWith(@"i:\")
                || uri.ToLower().StartsWith(@"j:\")
                || uri.ToLower().StartsWith(@"k:\")
                || uri.ToLower().StartsWith(@"l:\")
                || uri.ToLower().StartsWith(@"m:\")
                || uri.ToLower().StartsWith(@"n:\")
                || uri.ToLower().StartsWith(@"o:\")
                || uri.ToLower().StartsWith(@"p:\")
                || uri.ToLower().StartsWith(@"q:\")
                || uri.ToLower().StartsWith(@"r:\")
                || uri.ToLower().StartsWith(@"s:\")
                || uri.ToLower().StartsWith(@"t:\")
                || uri.ToLower().StartsWith(@"u:\")
                || uri.ToLower().StartsWith(@"v:\")
                || uri.ToLower().StartsWith(@"w:\")
                || uri.ToLower().StartsWith(@"x:\")
                || uri.ToLower().StartsWith(@"y:\")
                || uri.ToLower().StartsWith(@"z:\"))
                if (uri.LastIndexOf('.') > 0 && uri.LastIndexOf('.') + 1 < uri.Length && uri.LastIndexOf('\\') < uri.LastIndexOf('.'))
                    t = uri.ToLower().Substring(uri.LastIndexOf('.') + 1);
                else
                    if (uri.ToLower().StartsWith("http://"))
                        t = "http";
                    else
                        if (uri.ToLower().StartsWith("ftp://"))
                            t = "ftp";
                        else
                            if (uri.ToLower().StartsWith("notes://"))
                                t = "notes";

            return t;
        }

        public void Modify(SQLiteConnection c, string titre, string texte, string uri, string type, Label[] tags)
        {
            if (uri != null) uri = uri.Replace("'", "''");
            if (titre == "" || titre == null) throw new Exception("Titre vide");
            titre = titre.Replace("'", "''");
            if (texte != null) texte = texte.Replace("'", "''");

            string date = DateTime.Now.Year + "-" + DateTime.Now.Month.ToString("D2") + "-" + DateTime.Now.Day.ToString("D2");

            string sql = "UPDATE items_b SET uri=";
            sql += (uri!= null && uri !="")? "'"+uri+"'" : "null";
            sql += ", type=";
            sql += (type != null && type != "") ? "'" + type + "'" : "null";
            sql += ", quand='" + date + "' ";

            sql += " WHERE id=" + this._id;
            string sql2 = "UPDATE items_c SET titre='";
            sql2 += titre;
            sql2 += "', texte=";
            sql2+= (texte != null && texte != "") ? "'" + texte + "'" : "null";

            int nbrTag = tags.Length;
            string tagsString = "";

            for (int i = 0; i < nbrTag; i++)
                tagsString += tags[i].Tag + " ";

            sql2 += ", tags=" + (tagsString != "" ? "'" + tagsString + "'" : "null");
            sql2 += " WHERE ROWID=" + this._id;

            SQLiteCommand command = new SQLiteCommand(sql, c);
            try
            {
                command.Transaction = c.BeginTransaction();
                command.ExecuteNonQuery();
                command.CommandText = sql2;
                command.ExecuteNonQuery();

                command.CommandText = "DELETE FROM items_tags WHERE item_id = " + this._id;
                command.ExecuteNonQuery();

                int tagid = -1;

                for (int i = 0; i < nbrTag; i++)
                {
                    command.CommandText = "SELECT id FROM tags WHERE label = '" + tags[i].Tag.ToLower() + "'";
                    tagid = int.Parse(command.ExecuteScalar().ToString());



                    command.CommandText = "INSERT INTO items_tags (item_id, tag_id) VALUES (" + this._id + "," + tagid + ");";
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();

                this._titre = titre;
                this._texte = texte;
                this._uri = uri;
                this._type = type;
            }
            catch (SQLiteException ex)
            {

                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            }
            finally
            {
                command.Dispose();
            }
        }

        public void Delete(SQLiteConnection c)
        {
            SQLiteCommand command = new SQLiteCommand(c);

            try
            {
                command.Transaction = c.BeginTransaction();

                command.CommandText = "DELETE FROM items_tags WHERE item_id = " + this._id; ;
                command.ExecuteNonQuery(); //Supression des liens vers les tags
                
                command.CommandText = "DELETE FROM items_c WHERE ROWID = " + this._id;
                command.ExecuteNonQuery();
                
                command.CommandText = "DELETE FROM items_b WHERE id = " + this._id;
                command.ExecuteNonQuery();

                command.Transaction.Commit();
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
            }
            finally
            {
                command.Dispose();
            }
        }

        public static Item Create(SQLiteConnection c, string titre, string uri, string texte, string type, Label[] tags)
        {
            string date = DateTime.Now.Year + "-" + DateTime.Now.Month.ToString("D2") + "-" + DateTime.Now.Day.ToString("D2");

            if (uri != null) uri = uri.Replace("'", "''");
            if (titre == "" || titre == null) throw new Exception("Titre vide");
            titre = titre.Replace("'", "''");
            if (texte != null) texte = texte.Replace("'", "''");

            string sql = "INSERT INTO items_b (uri, type, quand) VALUES (";
            sql += (uri != "" && uri != null) ? "'" + uri + "'" : "null";
            sql += ", " + ((type != "" && type != null) ? "'" + type + "'" : "null");
            //sql += ", DATE() )";
            sql += ", '" + date + "')";
            string sql2 = "INSERT INTO items_c(rowid, titre, texte, tags) VALUES (last_insert_rowid(), '";
            sql2 += titre + "', ";
            sql2 += (texte != "" && texte != null) ? "'" + texte + "'" : "null";
            
            int nbrTag = tags.Length;
            string tagsString = "";

            for (int i = 0; i < nbrTag; i++)
                tagsString += tags[i].Tag + " ";

            sql2 += ", " + (tagsString != "" ? "'" + tagsString + "'" : "null") + ")";

            SQLiteCommand command = new SQLiteCommand(sql, c);

            try
            {
                command.Transaction = c.BeginTransaction();
                command.ExecuteNonQuery();

                int id = -1;

                command.CommandText = "SELECT last_insert_rowid()";
                id = int.Parse(command.ExecuteScalar().ToString());

                command.CommandText = sql2;
                command.ExecuteNonQuery();

                int tagid = -1;

                for (int i = 0; i < nbrTag; i++)
                {
                    command.CommandText = "SELECT id FROM tags WHERE label = '" + tags[i].Tag.ToLower() + "'";
                    tagid = int.Parse(command.ExecuteScalar().ToString());

                    command.CommandText = "INSERT INTO items_tags (item_id, tag_id) VALUES (" + id + "," + tagid + ");";
                    command.ExecuteNonQuery();
                }

                command.Transaction.Commit();
                command.Dispose();
                return new Item(id, titre, (uri != "" || uri != null) ? "'" + uri + "'" : "null", texte, date, (type != "" || type != null) ? "'" + type + "'" : "null", tags);
            }
            catch (SQLiteException ex)
            {
                command.Transaction.Rollback();
                System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                command.Dispose();
                return null;
            }
        }

        public override string ToString()
        {
            string d = _titre + " [";
            foreach (Label l in _tags)
            {
                d += l.Tag + " ";
            }
            d += "]";
            return d;
        }

        public static Item[] Seek(SQLiteConnection c, string req)
        {
            string sql=null;
            if (req != "")
            {
                MemoryStream ms = new MemoryStream(Encoding.GetEncoding(1252).GetBytes(req));
                Scanner scanner = new Scanner(ms);
                Parser parser = new Parser(scanner);

                try
                {
                    parser.Parse();
                    if (parser.errors.count != 0) throw new ParserException();
                    sql = parser.SQL+"  ORDER BY quand DESC";
                }
                catch (ParserException)
                {
                    throw new ParserException();
                }
                finally
                {
                   // ms.Dispose();
                }
            }
            else
            {
                sql = "SELECT * FROM items ORDER BY quand DESC";
            }

            if (sql != null && sql != "")
            {
                SQLiteCommand command = new SQLiteCommand(sql, c);
                Hashtable ht = new Hashtable();
                ArrayList al = new ArrayList();
                ArrayList alItem = new ArrayList();
                SQLiteDataReader dr2 = null;
                SQLiteCommand command2 = null;
                SQLiteDataReader dr = null;

                try
                {
                    //command.Transaction = c.BeginTransaction();
                    dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        command2 = new SQLiteCommand("SELECT tags.id, label FROM tags INNER JOIN items_tags ON (tags.id = items_tags.tag_id) AND items_tags.item_id =" + dr["id"].ToString() + " ORDER BY label", c);
                        command2.Transaction = command.Transaction;
                        dr2 = command2.ExecuteReader();
                        al.Clear();

                        while (dr2.Read())
                        {
                            al.Add(new Label(int.Parse(dr2["id"].ToString()), dr2["label"].ToString()));
                        }
                        alItem.Add(new Item(
                                int.Parse(dr["id"].ToString()),
                                    dr["titre"].ToString(),
                                    dr["uri"].ToString(),
                                    dr["texte"].ToString(),
                                    dr["quand"].ToString(),
                                    dr["type"].ToString(),
                                    (Label[])al.ToArray(typeof(Label))));
                        dr2.Dispose();
                    }
                    dr.Dispose();
                    //command.Transaction.Commit();
                    return (Item[])alItem.ToArray(typeof(Item));
                }
                catch (SQLiteException ex)
                {
                    //command.Transaction.Rollback();
                    System.Windows.Forms.MessageBox.Show(ex.Message, "Erreur", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                    return null;
                }
                finally
                {
                    if(command!=null) command.Dispose();
                    if(command2!=null) command2.Dispose();
                    if(dr2!=null) dr2.Dispose();
                    if(dr!=null) dr.Dispose();
                }
            }
            throw new ParserException();
        }
    }
}
